package com.cn.trip.DbDao;



import com.cn.trip.DaoUtil.DBHelper;
import com.cn.trip.bean.Cart;
import com.cn.trip.bean.Goods;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class CartDao {
	private Connection conn;
	private PreparedStatement ps;
	private ResultSet rs;
	
	public boolean exist(int user_id,int goods_id) {
		String sql="select id from cart where user_id=? and goods_id=?";
		conn= DBHelper.getConnection();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, user_id);
			ps.setInt(2, goods_id);
			rs=ps.executeQuery();
			if(rs.next()) {
				return true;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				DBHelper.closeAll(rs, ps, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return false;
	}
	
	public int addToCart(int user_id,int goods_id) {	
		String sql=String.format("insert into cart (user_id,goods_id,goods_num) values (%s,%s,1)",user_id,goods_id);
		conn=DBHelper.getConnection();
		try {
			ps=conn.prepareStatement(sql);
//			ps.setInt(1,user_id);
//			ps.setInt(2, goods_id);
			return ps.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				DBHelper.closeAll(rs, ps, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return 0;
	}
	
	public int updateCart(int user_id,int goods_id) {
		String sql=String.format("update cart set goods_num=goods_num+%s where user_id=%s and goods_id=%s",user_id,goods_id);
		conn=DBHelper.getConnection();
		try {
			ps=conn.prepareStatement(sql);
//			ps.setInt(1,user_id);
//			ps.setInt(2, goods_id);
			return ps.executeUpdate(sql);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				DBHelper.closeAll(rs, ps, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return 0;
	}
	public List<Cart> getCartList(int user_id){
		
		String sql="select cart.id,cart.goods_num,goods.id,goods.goods_name,goods.price,goods.imgsrc,goods.stock from goods left join cart on  goods.id=cart.goods_id where user_id=? ";
		List<Cart> list=new ArrayList<Cart>();
		conn=DBHelper.getConnection();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, user_id);
			rs=ps.executeQuery();
			while(rs.next()) {
				Cart c=new Cart();
				c.setId(rs.getInt(1));
				c.setUser_id(user_id);
				c.setGoods_num(rs.getInt(2));
				Goods g=new Goods(rs.getInt(3), rs.getString(4),rs.getFloat(5),rs.getString(6),rs.getInt(7));
				c.setGoods(g);
				list.add(c);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			try {
				DBHelper.closeAll(rs, ps, conn);
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}
	
	public int deleteByUserID(int user_id) {
		String sql="delete from cart where user_id=?";
		conn=DBHelper.getConnection();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, user_id);
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	
	public int deleteByID(int id) {
		String sql="delete from cart where id=?";
		conn=DBHelper.getConnection();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
	
	public int updateNum(int id,int num) {
		String sql="update cart set goods_num=? where id=?";
		conn=DBHelper.getConnection();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, num);
			ps.setInt(2, id);
			return ps.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return 0;
	}
}
